﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace SW_SoftBully.Models.ModuloAdministracion.Convocatoria


{
    public class ConvocatoriaDAO
    {




        
        public string modificarConvocatoria(ConvocatoriaBean convocatoriaBean)
        {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;
            List<Tema> listaCargo = new List<Tema>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {
              SqlCommand sqlCmd ;
                string commandString="";
                   commandString = "update convocatoria set Fecha='"+ convocatoriaBean.fecha+"' , Lugar ='"  + convocatoriaBean.lugar + "' where id_convocatoria= " + convocatoriaBean.id_convocatoria;
                   sqlCmd = new SqlCommand(commandString, sqlCon);
                  sqlCmd.ExecuteNonQuery();
                  commandString = "delete convocatoria_x_tema  where id_convocatoria= " + convocatoriaBean.id_convocatoria;
                  sqlCmd = new SqlCommand(commandString, sqlCon);
                  sqlCmd.ExecuteNonQuery();  


                String[] aux;
                  aux = convocatoriaBean.temas.Split('|');
                  for (int i = 0; i < aux.Count(); i++)
                  {
                      if (!aux[i].Equals(""))
                      {
                          commandString = "insert into convocatoria_x_tema values(" + convocatoriaBean.id_convocatoria + ",'" + aux[i] + "')";
                          sqlCmd = new SqlCommand(commandString, sqlCon);
                          sqlCmd.ExecuteNonQuery();
                      }
                  }
                







                sqlCon.Close();

               

                return "Se modifico la convocatoria correctamente";
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return "Se produjo un error al modificar la convocatoria";

            }


        }

        


        public string insertarConvocatoria(ConvocatoriaBean convocatoriaBean)
        {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;
            List<Tema> listaCargo = new List<Tema>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {
                int id_convocatoria=0;
                string commandString = "select max(id_convocatoria) from convocatoria";
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                if (!Convert.IsDBNull(sqlCmd.ExecuteScalar()))
                {
                    id_convocatoria = (int)sqlCmd.ExecuteScalar();
                }
                else
                {

                    id_convocatoria = 0;
                }
                id_convocatoria++;

                   commandString = "insert into convocatoria (Id_Junta_propietario,id_convocatoria,fecha,lugar,estado,id_edificio) values (2," + id_convocatoria + ",'" + convocatoriaBean.fecha + "','" + convocatoriaBean.lugar + "',1," + convocatoriaBean.id_edificio + ")";
                   sqlCmd = new SqlCommand(commandString, sqlCon);
                  sqlCmd.ExecuteNonQuery();  
                String[] aux;
                  aux = convocatoriaBean.temas.Split('|');
                  for (int i = 0; i < aux.Count(); i++)
                  {
                      if (!aux[i].Equals(""))
                      {
                          commandString = "insert into convocatoria_x_tema values(" + id_convocatoria + ",'" + aux[i] + "')";
                          sqlCmd = new SqlCommand(commandString, sqlCon);
                          sqlCmd.ExecuteNonQuery();
                      }
                  }
                







                sqlCon.Close();

               

                return "Se inserto la convocatoria correctamente";
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return "Se produjo un error al insertar la convocatoria";

            }


        }

        
            
        
        [HttpPost]
        public List< string> buscarTema(int id_convocatoria)
        {
             String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;
            List<string> listaTema = new List<string>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {

                string commandString = "select distinct tema from convocatoria_x_tema where id_convocatoria= " + id_convocatoria;

               
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
                
                while (dataReader.Read())
                {
                    
                 
                    
                    listaTema.Add((string)dataReader["tema"]);
                }
                dataReader.Close();




                sqlCon.Close();

                return listaTema;
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return null;

            }


        }


        public string eliminar(int id_convocatoria)
        {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;

            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {

                string commandString = "";
                SqlCommand sqlCmd;
                commandString = "update convocatoria set estado=0 where id_convocatoria = " + id_convocatoria;
                sqlCmd = new SqlCommand(commandString, sqlCon);
                sqlCmd.ExecuteNonQuery();





                sqlCon.Close();



                return "Se elimino la convocatoria correctamente";
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return "Se produjo un error al eliminar la convocatoria";

            }


        }


    
    



        [HttpPost]
        public List< ConvocatoriaBean> buscarConvocatoria(Filtro filtroo)
        {
             String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["SoftBully"].ConnectionString;
            List<ConvocatoriaBean> listaConvocatoria = new List<ConvocatoriaBean>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {

                string commandString = "select * from Convocatoria where Estado=1  ";

                string filtro="";

             if (!(filtroo.lugar == null || filtroo.lugar.Trim().Equals("")))
                {
                    filtro = filtro + " and  lugar like '%" + filtroo.lugar.Trim() + "%'  ";
                }


             if (!(filtroo.fechaInicial == null || filtroo.fechaInicial.Equals(Convert.ToDateTime("01/01/0001 12:00:00 AM"))))
                {
                    filtro = filtro + " and  Fecha >= '" + filtroo.fechaInicial + "'  ";
                }


             if (!(filtroo.fechaFinal == null || filtroo.fechaFinal.Equals(Convert.ToDateTime("01/01/0001 12:00:00 AM"))))
                {
                    filtro = filtro + " and  Fecha <= '" + filtroo.fechaFinal + "'  ";
                }

                commandString+=filtro + " order by fecha desc";
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
                DateTime a;
                while (dataReader.Read())
                {
                    ConvocatoriaBean con = new ConvocatoriaBean();
                    //cliente. = (int)dataReader["idUsuario"];


                    con.lugar = (string)dataReader["lugar"];
                    a = (DateTime)dataReader["fecha"];
                    con.fecha = a.ToShortDateString();
                    con.id_convocatoria = (int)dataReader["id_convocatoria"];
                    con.id_edificio = (int)dataReader["id_edificio"];
                    con.id_juntaPropietario = (int)dataReader["id_junta_propietario"];
                    
                    listaConvocatoria.Add(con);
                }
                dataReader.Close();




                sqlCon.Close();

                return listaConvocatoria;
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return null;

            }


        }

        


    
    }

}

      